<?php

class GenealogyController extends Controller
{
	var $network_height = 9;

	public function actionIndex()
	{
		$this->logAction();
		$this->render('index');
	}

	public function actionTotalbv() 
	{
		$this->render('totalbv');

	}
	
	public function actionVolumes()
	{
		$this->logAction();
		$this->render('volumes');
	}
	
	public function getMember( $member_id ) 
	{
		$connection = Yii::app()->db;
   
		$sql = "SELECT member_id, first_name, last_name, middle_name,
		               main_address, email, mobile_no, sponsor_id  
		          FROM gn_members 
				 WHERE member_id=$member_id";
				 
		$command = $connection->createCommand($sql);
		$dataReader = $command->query();
		
		$data = NULL;
		
		foreach( $dataReader as $row ) { 
		   $data = $row; 
        }
		
		return $data;
    }
	
	public function getChildren( $member_id ) 
	{
		$connection = Yii::app()->db;
 
		$sql = "SELECT member_id, first_name, last_name, position, sponsor_id 
		          FROM gn_members 
				 WHERE sponsor_id=$member_id";
				 
		$command = $connection->createCommand($sql);
		$dataReader = $command->query();
		
		$members = array();
		
		foreach( $dataReader as $row ) {
		   $members[$row['position']] = $row;
		}
		
		return $members;
	}
	
	public function getDownlines( $parent_id, &$downlines ) 
	{
		$connection = Yii::app()->db;
 
		$sql = "SELECT member_id, first_name, last_name, position, sponsor_id 
		          FROM gn_members 
				 WHERE sponsor_id=$parent_id";
		$command = $connection->createCommand($sql);
		$dataReader = $command->query();
		
		foreach( $dataReader as $row ) {
		   array_push( $downlines, $row );
		   $this->getDownlines( $row['member_id'], $downlines );
		}
	}
	
	public function getVolumeDownlines( $parent_id, $year, $month_no, $level, &$downlines, &$total_points ) 
	{
		if ($level==$this->network_height) return;

		$connection = Yii::app()->db;
 
		$sql = "SELECT member_id, first_name, last_name, position, sponsor_id 
		          FROM gn_members 
				 WHERE sponsor_id=$parent_id";
				 
		$command = $connection->createCommand($sql);
		$dataReader = $command->query();
		
		foreach( $dataReader as $row ) {
		   $bv_personal = $this->getMemberPersonalBV( $row['member_id'], $year, $month_no);
		   
		   //if ( $bv_personal )
		   //{
		   	  $row['bv_personal'] = $bv_personal;
			  $total_points += $bv_personal;
		   	  array_push( $downlines, $row );
		   //}
		   
		   $this->getVolumeDownlines( $row['member_id'], $year, $month_no, $level++, $downlines, $total_points );
		}
	}

	public function getTotalVolumeDownlines( $parent_id, $level, &$downlines, &$total_points ) 
	{
		if ($level==$this->network_height) return;

		$connection = Yii::app()->db;
 
		$sql = "SELECT member_id, first_name, last_name, position, sponsor_id 
		          FROM gn_members 
				 WHERE sponsor_id=$parent_id";
				 
		$command = $connection->createCommand($sql);
		$dataReader = $command->query();
		
		foreach( $dataReader as $row ) {
		   $bv_total = $this->getMemberTotalBV( $row['member_id']);
		   
		   //if ( $bv_personal )
		   //{
		      //echo "level: $level + ".$row['first_name'].", ";
		   	  $row['bv_total'] = $bv_total;
			  $total_points += $bv_total;
		   	  array_push( $downlines, $row );
		   //}
		   
		   $this->getTotalVolumeDownlines( $row['member_id'], $level++, $downlines, $total_points );
		}
	}
	
	public function getMemberPersonalBV($member_id=0, $year=0, $month_no=0 )
	{
		$connection = Yii::app()->db;
		
		$sql = "SELECT bv_personal 
		          FROM gn_payouts  
		         WHERE member_id=$member_id
		           AND month_no=$month_no
		           AND year=$year";
					  
		$command = $connection->createCommand($sql);
	    $data = $command->query()->read();   
	    
	    if ( isset($data['bv_personal']) )
	       return $data['bv_personal'];
        else
           return 0;
	}

	public function getMemberTotalBV($member_id=0)
	{
		$connection = Yii::app()->db;
		
		$sql = "SELECT SUM(bv_personal) as bv_total 
		          FROM gn_payouts  
		         WHERE member_id=$member_id";
					  
		$command = $connection->createCommand($sql);
	    $data = $command->query()->read();   
	    
	    if ( isset($data['bv_total']) )
	       return $data['bv_total'];
        else
           return 0;
	}
	
	public function array_implode( $glue, $separator, $array ) {
		if ( ! is_array( $array ) ) return $array;
		$string = array();
		foreach ( $array as $key => $val ) {
			if ( is_array( $val ) )
				$val = implode( ',', $val );
			$string[] = "{$key}{$glue}{$val}";
		   
		}
		
		return implode( $separator, $string );
	}
	
	public function logAction()
	{
		$datetime = date("Y-m-d H:i:s");
		$user = Yii::app()->user->id;
		$action =  $this->id . '/' . $this->action->id;
		$data   = $this->array_implode( "=", "::", $_GET );
		$data  .= $this->array_implode( "=", "::", $_POST );
		
		$sql = "INSERT INTO gn_audit_trail SET
		               datetime='$datetime', 
					   user='$user',
					   action='$action',
					   data='$data'";
					   
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql);
		$dataReader = $command->query();
		
	}
	

	// Uncomment the following methods and override them if needed
	/*
	public function filters()
	{
		// return the filter configuration for this controller, e.g.:
		return array(
			'inlineFilterName',
			array(
				'class'=>'path.to.FilterClass',
				'propertyName'=>'propertyValue',
			),
		);
	}

	public function actions()
	{
		// return external action classes, e.g.:
		return array(
			'action1'=>'path.to.ActionClass',
			'action2'=>array(
				'class'=>'path.to.AnotherActionClass',
				'propertyName'=>'propertyValue',
			),
		);
	}
	*/
	
	public function actionExcelGenealogy()
	{
		$parent = $this->getMember( $_GET['id'] );
		$parent_header  = '['.$parent['member_id'].'] '.$parent['last_name'].', '.$parent['first_name'];
		$parent_header .= ' '.$parent['middle_name'];
		
		$l_downlines = array();
		$r_downlines = array();
		
		$children = $this->getChildren( $_GET['id'] );
	
		if ( isset($children['left']) ) {
		  $data = $children['left'];	
		  array_push( $l_downlines, $data );
		  $this->getDownlines( $data['member_id'], $l_downlines );
		}
		
		if ( isset($children['right']) ) {
		  $data = $children['right'];	
		  array_push( $r_downlines, $data );
		  $this->getDownlines( $data['member_id'], $r_downlines );
		}
	
		$l_total = count($l_downlines);
		$r_total = count($r_downlines);
		
		$downlines = array ();
		
		if ( $l_total > 1 ) {
			foreach ( $l_downlines as $node ) {
			  
			  $data=array();	
			  $data['l_id'] = $node['member_id'];
			  $data['l_first_name'] = $node['first_name'];
			  $data['l_last_name']  = $node['last_name'];
			  $data['l_sponsor_id'] = $node['sponsor_id'];
			  $data['div'] = "";
			  $data['r_id'] ="";
			  $data['r_first_name'] = "";
			  $data['r_last_name']  = "";
			  $data['r_sponsor_id'] = "";
			  
			  array_push( $downlines, $data );
			}
		}
		
		
		if ( $r_total > 1 ) {
			$i=0;
			foreach ( $r_downlines as $node ) {
			  
			  if ( isset($downlines[$i]) ) {
			      $downlines[$i]['r_id'] = $node['member_id'];
			      $downlines[$i]['r_first_name'] = $node['first_name'];
			      $downlines[$i]['r_last_name']  = $node['last_name'];
			      $downlines[$i]['r_sponsor_id'] = $node['sponsor_id'];
			  }
			  else
			  {
				  $data=array();
				  $data['l_id'] ="";
				  $data['l_first_name'] = "";
				  $data['l_last_name']  = "";
				  $data['l_sponsor_id'] = "";
				  $data['div'] = "";
				  $data['r_id'] = $node['member_id'];
				  $data['r_first_name'] = $node['first_name'];
				  $data['r_last_name']  = $node['last_name'];
				  $data['r_sponsor_id'] = $node['sponsor_id'];
				  array_push( $downlines, $data );
			  }
			  
			  $i++;
			  
			}
		}
		
		$data=array();
	    $data['l_id'] ="";
	    $data['l_first_name'] = "";
	    $data['l_last_name']  = "";
	    $data['l_sponsor_id'] = "";
	    $data['div'] = $parent_header;
	    $data['r_id'] = "";
	    $data['r_first_name'] = "";
	    $data['r_last_name']  = "";
	    $data['r_sponsor_id'] = "";
		
		array_unshift( $downlines, $data );
		
		//print_r ( $downlines );
		
		$dataProvider = new CArrayDataProvider($downlines);
	    $this->toExcel($dataProvider); 
		
	}
	
	public function behaviors(){
	
	    return array(
            'eexcelview'=>array(
                'class'=>'ext.eexcelview.EExcelBehavior',
            ),
        );
	}

   
	public function getPersonalBV ( $member_id ) 
	{
		$connection = Yii::app()->db;

		$year = $this->year;
		$month_no = $this->month_no;
		
		
		$sql = "SELECT a.member_id, SUM(a.quantity*b.bv_points) as points
		               FROM gn_sales AS a 
		          LEFT JOIN gn_products AS b 
		                 ON (a.product_id=b.product_id)
		              WHERE (WEEK(a.dt_sale)=$month_no AND YEAR(a.dt_sale)=$year) 
		           GROUP BY a.member_id";
		
		$command = $connection->createCommand($sql);
	    $dataReader = $command->query();

	    while ( $data = $dataReader->read() ) 
	    {
	    	//print_r($data);
			
	    	$sql= "";
	        $payout_id = $this->get_gnPayoutsID($year, $month_no, $data['member_id']);
	    	
	    	if ( $payout_id > 0 ) 
			{
			   #update
			   $bv_personal = $data['points'];
			   if ( $bv_personal < 1 )
			     $bv_personal=0;
			   
			   $sql = "UPDATE gn_payouts 
			              SET bv_personal=$bv_personal
			            WHERE payout_id=$payout_id";	
			}
			else 
			{
			   #insert	
			   $member_id = $data['member_id'];
			   $bv_personal = $data['points'];
			   $sql = "INSERT INTO gn_payouts 
			                   SET member_id=$member_id,
			                       bv_personal=$bv_personal,
			                       year=$year,
			                       month_no=$month_no,
			                       status='pending'";
			}
			
			$connection->createCommand($sql)->query();
	    }	  
	}
	
	public function getLeftRightBV () {
		$connection = Yii::app()->db;
		
		$year = $this->year;
		$month_no = $this->month_no;
		
		#get only active members status=1
		$sql = "SELECT a.member_id  
		               FROM gn_members AS a
		    LEFT OUTER JOIN gn_payouts AS b
		                 ON (a.member_id=b.member_id)
		              WHERE b.month_no=$month_no AND b.year=$year
		                AND a.status_id=1";
				   
		$command = $connection->createCommand($sql);
	    $dataReader = $command->query();

	    while ( $data = $dataReader->read() ) 
	    {
	       //print_r($data);
	       $member_id = $data['member_id']; 
	       $child = $this->getChildren($member_id, $year, $month_no);
		   
		   //print_r ($child);
		   
	       $bv_left = 0;
	       $bv_right = 0; 
	       
	       if ( isset($child['left']) )
		   {
		   	 $bv_left = $child['left']['bv_personal'];
	         $this->getDownlineBV($child['left']['member_id'], 1, $bv_left );
	       } 
	         
		   
		   if ( isset($child['right']) )
	       {
	       	  $bv_right = $child['right']['bv_personal'];
	       	  $this->getDownlineBV($child['right']['member_id'], 1, $bv_right );
	       }  
	         
	       $payout_id = $this->get_gnPayoutsID($year, $month_no, $data['member_id']);
	       
	       #remove carryover by sir ador July 2, 2012
	       //$carryover = $this->getPrevWeekCarryOver($member_id);
	       
	       //$bv_left += $carryover['bv_left_carryover'];
	       //$bv_right += $carryover['bv_right_carryover'];
	       
	       $sql = "UPDATE gn_payouts 
	                  SET bv_left=$bv_left, bv_right=$bv_right 
	                WHERE payout_id=$payout_id";
		   
		   $connection->createCommand($sql)->query();
		}
	}
	
}